<?php

/**
 * 数据库操作类
 * @author keluo<pycorvn@yeah.net>
 * @since 2015-02-02
 */
class SCDb {

    protected $_connect = null;
    protected $_table = null;
    protected $_semantics;
    private $pdo_statement = null;
    private $result = null;
    private $dataList = null;

    const FETCH_COLUMN = PDO::FETCH_COLUMN; // 取出第一列数据
    const FETCH_ASSOC = PDO::FETCH_ASSOC; // 以数组形式返回数据
    const FETCH_OBJ = PDO::FETCH_OBJ; // 以对象形式返回数据

    public function __construct($connect = null) {
        $this->_connect = is_null($connect) ? SCS::config()->connect : $connect;
        $this->_semantics = new stdClass();
    }

    /**
     * 切换数据库连接
     * @param string $connect
     */
    public function connect($connect) {
        $this->_connect = $connect;
        $this->resetSemantics();
        return $this;
    }

    /**
     * 重置SQL语义树
     */
    public function reset() {
        $this->resetSemantics();
        return $this;
    }

    /**
     * author   thk
     * 查询数据并检查
     */
    public function checkData() {
        $_l = SCS::db()->from($this->table)->where($this->where)->fetch(SCDb::FETCH_OBJ);
        return $this;
    }

    /**
     * 需要查询的字段列表
     * @param string $fields 字段列表
     */
    public function select($fields = '*') {
        $this->_semantics->field = trim(trim($fields), ',');
        return $this;
    }

    /**
     * 选择数据表
     * @param string $table 表名
     * @param string $alias 表别名
     */
    public function from($table, $alias = '') {
        if (preg_match('/^\{.*\}$/s', $table)) { // 表全名 ，如'{SC_Admin}'
            $this->_table = substr($table, 1, -1);
        } else { // 默认自动加表前缀
            $this->_table = SCS::Config()->db[$this->_connect]['prefix'] . $table;
        }
        if ($alias)
            $this->_table .= ' AS ' . $alias;
        return $this;
    }

    /**
     * 左连接数据表
     * @param string $table 表名
     * @param string $condition 连接条件
     * @param string $alias 表别名
     */
    public function leftJoin($table, $condition, $alias = '') {
        if (preg_match('/^\{.*\}$/s', $table)) { // 表全名 ，如'{SC_Admin}'
            $table = substr($table, 1, -1);
        } else { // 默认自动加表前缀
            $table = SCS::Config()->db[$this->_connect]['prefix'] . $table;
        }
        if ($alias)
            $table .= ' AS ' . $alias;

        $this->_semantics->leftJoin[] = array($table, $condition);
        return $this;
    }

    /**
     * 内连接数据表
     * @param string $table 表名
     * @param string $condition 连接条件
     * @param string $alias 表别名
     */
    public function innerJoin($table, $condition, $alias = '') {
        if (preg_match('/^\{.*\}$/s', $table)) { // 表全名 ，如'{SC_Admin}'
            $table = substr($table, 1, -1);
        } else { // 默认自动加表前缀
            $table = SCS::Config()->db[$this->_connect]['prefix'] . $table;
        }
        if ($alias)
            $table .= ' AS ' . $alias;
        $this->_semantics->innerJoin[] = array($table, $condition);
        return $this;
    }

    /**
     * SQL WHERE条件
     * @param string $where
     */
    public function where($where) {
        if (trim($where))
            $this->_semantics->where = trim($where);
        return $this;
    }

    /**
     * SQL ORDER BY条件
     * @param string $order
     */
    public function order($order) {
        if (trim($order))
            $this->_semantics->order = trim($order);
        return $this;
    }

    /**
     * SQL GROUP BY条件
     * @param string $group
     */
    public function group($group) {
        if (trim($group))
            $this->_semantics->group = trim($group);
        return $this;
    }

    /**
     * SQL HAVING条件
     * @param string $having
     */
    public function having($having) {
        if (trim($having))
            $this->_semantics->having = trim($having);
        return $this;
    }

    /**
     * SQL LIMIT条件
     * @param integer $start
     * @param integer $num
     */
    public function limit($start, $num = null) {
        if (is_null($num)) {
            $this->_semantics->limit = $start;
        } else {
            $this->_semantics->limit = $start . ', ' . $num;
        }
        return $this;
    }

    /**
     * 设定参数绑定列表
     * @param array $value : array(':id' => 11, ':name' => 'hello')
     */
    public function bindValue(array $value = null) {
        $this->_semantics->value = $value;
        return $this;
    }

    /**
     * 返回单条查询记录
     * 如果仅返回第一列的值，请将$fetch_type设置为SCDb::FETCH_COLUMN
     * @example
      $list = SCS::db()->from('manage_operators o')
      ->leftJoin('manage_game as g', 'o.code = g.code')
      ->where('o.op_id > :op_id and o.name like :name')
      ->order('g.game_id DESC')
      ->bindValue(array(':op_id' => 1, ':name' => '%1%'))
      ->fetch();
     * @param array $params 需绑定的参数列表 
     * @param const $fetch_type 数据返回类型，建议设置为SCDb::FETCH_OBJ或SCDb::FETCH_ASSOC
     * @return array
     * @throws ErrorException
     */
    public function fetch($fetch_type = SCDb::FETCH_OBJ) {
        $sql = 'SELECT ';
        $sql .= isset($this->_semantics->field) ? $this->_semantics->field : '*';
        $sql .= ' FROM ' . $this->_table;
        $sql .= $this->jointSql();
        $sql .= ' LIMIT 1';

        try {
            $this->dataList = $this->execute($sql)->pdo_statement->fetch($fetch_type);
            return $this;
        } catch (PDOException $e) {
            throw new Exception($e->getMessage() . ' [SQL: ' . $sql . ']');
        }
    }

    /**
     * 返回多条查询记录
     * 如果仅返回每一行第一列的值，请将$fetch_type设置为SCDb::FETCH_COLUMN 
     * @param const $fetch_type 数据返回类型，建议设置为SCDb::FETCH_OBJ或SCDb::FETCH_ASSOC
     * @return array
     * @throws ErrorException
     */
    public function fetchAll($fetch_type = SCDb::FETCH_OBJ) {
        $sql = 'SELECT ';
        $sql .= isset($this->_semantics->field) ? $this->_semantics->field : '*';
        $sql .= ' FROM ' . $this->_table;
        $sql .= $this->jointSql();
        $sql .= isset($this->_semantics->limit) ? ' LIMIT ' . $this->_semantics->limit : '';

        try {
            $this->dataList = $this->execute($sql)->pdo_statement->fetchAll($fetch_type);
            return $this;
        } catch (PDOException $e) {
            throw new Exception($e->getMessage() . ' [SQL: ' . $sql . ']');
        }
    }

    /**
     *
     * 分页查询数据
     * @param integer $page_size 每页显示记录数 
     * @param const $fetch_type 数据返回类型，建议设置为SCDb::FETCH_OBJ或SCDb::FETCH_ASSOC
     * @return array
     * @throws ErrorException
     */
    public function fetchPage($page_size = null, $fetch_type = SCDb::FETCH_OBJ) {
        if ($page_size)
            SCS::page()->size = $page_size;
        $sql = 'SELECT SQL_CALC_FOUND_ROWS ';
        $sql .= isset($this->_semantics->field) ? $this->_semantics->field : '*';
        $sql .= ' FROM ' . $this->_table;
        $sql .= $this->jointSql();
        $sql .= ' LIMIT ' . (SCS::page()->current - 1) * SCS::page()->size . ', ' . SCS::page()->size;

        try {
            $this->dataList = $this->execute($sql)->pdo_statement->fetchAll($fetch_type);

            $this->execute('SELECT FOUND_ROWS()');

            SCS::page()->total = intval($this->pdo_statement->fetchColumn());

            return $this;
        } catch (PDOException $e) {
            throw new Exception($e->getMessage() . ' [SQL: ' . $sql . ']');
        }
    }

    /**
     * 返回单条查询记录(原生SQL查询方式)
     * 如果仅返回第一列的值，请将$fetch_type设置为SCDb::FETCH_COLUMN
     * @param string $sql SQL语句 
     * @param const $fetch_type 数据返回类型，建议设置为SCDb::FETCH_OBJ或SCDb::FETCH_ASSOC
     * @return array
     * @throws ErrorException
     */
    public function find($sql, $fetch_type = SCDb::FETCH_OBJ) {
        $sql = preg_replace('/\{prefix\}/', SCS::Config()->db[$this->_connect]['prefix'], $sql);
        try {
            $this->dataList = $this->execute($sql)->pdo_statement->fetch($fetch_type);
            return $this;
        } catch (PDOException $e) {
            throw new Exception($e->getMessage() . ' [SQL: ' . $sql . ']');
        }
    }

    /**
     * 返回多条查询记录
     * 如果仅返回每一行第一列的值，请将$fetch_type设置为SCDb::FETCH_COLUMN
     * @param string $sql SQL语句 
     * @param const $fetch_type 数据返回类型，建议设置为SCDb::FETCH_OBJ或SCDb::FETCH_ASSOC
     * @return array
     * @throws ErrorException
     */
    public function findAll($sql, $fetch_type = SCDb::FETCH_OBJ) {
        $sql = preg_replace('/\{prefix\}/', SCS::Config()->db[$this->_connect]['prefix'], $sql);
        try {
            $this->dataList = $this->execute($sql)->pdo_statement->fetchAll($fetch_type);
            ;
            return $this;
        } catch (PDOException $e) {
            throw new Exception($e->getMessage() . ' [SQL: ' . $sql . ']');
        }
    }

    /**
     * 分页查询数据
     * @param string $sql SQL语句
     * @param integer $page 每页显示记录数
     * @param const $fetch_type 数据返回类型，建议设置为SCDb::FETCH_OBJ或SCDb::FETCH_ASSOC
     * @return array
     * @throws ErrorException
     */
    public function findPage($sql, $page_size = null, $fetch_type = SCDb::FETCH_OBJ) {
        if ($page_size)
            SCS::page()->size = $page_size;
        $sql = preg_replace('/\{prefix\}/', SCS::Config()->db[$this->_connect]['prefix'], $sql);
        $sql = preg_replace(array('/(?<=SELECT\s)(.*)(?=\sFROM)/is', '/\sLIMIT\s.*/is'), array(' SQL_CALC_FOUND_ROWS \1', ''), $sql); // 换行符的影响
        $sql .= ' LIMIT ' . (SCS::page()->current - 1) * SCS::page()->size . ', ' . SCS::page()->size;

        try {
            $this->dataList = $this->execute($sql)->pdo_statement->fetchAll($fetch_type);

            $this->execute('SELECT FOUND_ROWS()');

            SCS::page()->total = intval($this->pdo_statement->fetchColumn());
            return $this;
        } catch (PDOException $e) {
            throw new Exception($e->getMessage() . ' [SQL: ' . $sql . ']');
        }
    }

    /**
     * 插入数据
     * @param array $data 一维或二维数组
     * @return integer 主键ID
     */
    public function insert($data = null) {
        $attrs = array(0 => null);
        if (null === $data) {
            $attrs[0] = SCRequest::post();
        } elseif (is_array($data)) {
            if (!isset($data[0])) {
                $attrs[0] = $data;
            } else {
                $attrs = $data;
            }
        }
        if (!$attrs[0])
            throw new Exception('插入的数据是空的');

        $fields = array();
        foreach ($attrs as $a) {
            $fields = array_unique(array_merge($fields, array_keys($a)));
        }
        $table_fields = $this->getTableStruct($this->_table);
        $fields = array_intersect($fields, $table_fields);
        $autoCreateTime = false;
        if (in_array('fdCreate', $table_fields) && !in_array('fdCreate', $fields)) {
            $fields[] = 'fdCreate';
            $autoCreateTime = true;
        }
        $values = array();
        foreach ($attrs as $attr) {
            $value = '';
            foreach ($fields as $field) {
                if (array_key_exists($field, $attr) && !is_array($attr[$field]) && !is_object($attr[$field])) {
                    if (preg_match('/^\{.*\}$/s', $attr[$field])) { // 特殊处理标识,如'count' => '{count + 1}'
                        $value .= $this->escape(substr($attr[$field], 1, -1)) . ', ';
                    } else {
                        $value .= is_null($attr[$field]) ? 'NULL, ' : '"' . $this->escape($attr[$field]) . '", ';
                    }
                } elseif (($field = 'fdCreate') && $autoCreateTime) {
                    $value .= 'NOW(), ';
                } else {
                    $value .= 'NULL, ';
                }
            }

            $values[] = '(' . rtrim($value, ', ') . ')';
        }

        $sql = 'INSERT INTO `' . trim($this->_table, '`') . '`';
        $sql .= ' (`' . implode('`, `', $fields) . '`)';
        $sql .= ' VALUES ' . implode(', ', $values);
        try {
            $this->execute($sql);
            return $this;
        } catch (PDOException $e) {
            throw new Exception($e->getMessage() . ' [SQL: ' . $sql . ']');
        }
    }

    /**
     * 更新数据
     * @param array $data
     * @param boolean $whole 是否整表更新(该参数作用是防止未加WHERE条件引起的灾难性后果)
     * @return integer 更新受影响的记录数
     */
    public function update($data = null, $whole = false) {
        if (null === $data)
            $data = SCRequest::post();
        if (!$data)
            throw new Exception('更新的数据是空的');

        $table_fields = $this->getTableStruct($this->_table);
        $sets = array();
        foreach ($data as $field => $value) {
            if (!in_array($field, $table_fields))
                continue;
            if (is_array($value) || is_object($value))
                continue;
            if (preg_match('/^\{.*\}$/s', $value)) { // 特殊处理标识,如'count' => '{count + 1}'
                $sets[] = '`' . $field . '` = ' . substr($this->escape($value), 1, -1);
            } else {
                $sets[] = '`' . $field . '` = ' . (is_null($value) ? 'NULL' : ('"' . $this->escape($value)) . '"');
            }
        }

        if (empty($sets))
            return 0;
        $sql = 'UPDATE `' . trim($this->_table, '`') . '` SET ' . implode(', ', $sets);
        if (!isset($this->_semantics->where) && !$whole) { // 不允许整表更新，需强制加条件，防止误更新
            throw new Exception('未带条件的整表(' . $this->_table . ')更新操作已被禁止，确认执行此操作，' .
            '请在调用update()方法时设置第二个参数为true');
        } else {
            $sql .= ' WHERE ' . $this->_semantics->where;
        }

        $sql .= isset($this->_semantics->order) ? ' ORDER BY ' . $this->_semantics->order : '';
        $sql .= isset($this->_semantics->limit) ? ' LIMIT ' . $this->_semantics->limit : '';

        try {
            $this->execute($sql);
            return $this;
        } catch (PDOException $e) {
            throw new Exception($e->getMessage() . ' [SQL: ' . $sql . ']');
        }
    }

    /**
     * 删除数据
     * @param boolean $whole 是否删除整张表(该参数作用是防止未加WHERE条件引起的灾难性后果)
     * @return integer 更新受影响的记录数
     */
    public function delete($whole = false) {
        $sql = 'DELETE FROM `' . trim($this->_table, '`') . '`';
        if (!isset($this->_semantics->where) && !$whole) { // 不允许整表删除，需强制加条件，防止误删
            throw new Exception('未带条件的操作可能会删除整张表：' . $this->_table .
            '，确认执行此操作，请使用delete(true)显式告知');
        } else {
            $sql .= isset($this->_semantics->where) ? (' WHERE ' . $this->_semantics->where) : '';
        }
        $sql .= isset($this->_semantics->order) ? ' ORDER BY ' . $this->_semantics->order : '';
        $sql .= isset($this->_semantics->limit) ? ' LIMIT ' . $this->_semantics->limit : '';

        try {
            $this->execute($sql);
            return $this;
        } catch (PDOException $e) {
            throw new Exception($e->getMessage() . ' [SQL: ' . $sql . ']');
        }
    }

    /**
     * 执行SQL语句
     * @param string $sql SQL语句
     * @return 受影响记录数
     */
    public function execute($sql) {
        $this->result = null;
        try {
            $this->pdo_statement = $this->dbh()->prepare($sql);
            $this->boundValue();
            $this->result = $this->pdo_statement->execute();
            return $this;
        } catch (PDOException $e) {
            throw new Exception($e->getMessage() . ' [SQL: ' . $this->escape($sql) . ']');
        }
    }

    public function getResult() {
        return $this->result;
    }

    public function getData() {
        return $this->dataList;
    }

    /**
     * 返回最后一条插入数据的主键ID
     */
    public function lastInsertId() {
        return $this->dbh()->lastInsertId();
    }

    /**
     * 开始事务处理
     */
    public function beginTransaction() {
        $this->dbh()->setAttribute(PDO::ATTR_AUTOCOMMIT, FALSE);
        $this->dbh()->beginTransaction();
    }

    /**
     * 撤销事务
     */
    public function rollBack() {
        $this->dbh()->rollBack();
    }

    /**
     * 提交事务
     */
    public function commit() {
        $this->dbh()->commit();
    }

    /**
     * 重置SQL语义构造树
     */
    private function resetSemantics() {
        $this->_semantics = new stdClass();
        $this->_table = null;
    }

    /**
     * 获取到数据库的连接
     */
    private function dbh() {
        static $connectors = array();
        if (!isset($connectors[$this->_connect])) {
            $config = SCS::config()->db[$this->_connect];
            try {
                $connectors[$this->_connect] = new PDO($config['dsn'], $config['username'], $config['password'], array(PDO::ATTR_PERSISTENT => false));
            } catch (PDOException $e) {
                throw new Exception('数据库连接失败: ' . $e->getMessage());
            }

            $connectors[$this->_connect]->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // 设置异常抛出方式
            $connectors[$this->_connect]->query('SET NAMES utf8'); // 设置数据库查询编码
        }

        return $connectors[$this->_connect];
    }

    /**
     * 拼接SQL
     * @return string
     */
    private function jointSql() {
        $sql = '';
        if (isset($this->_semantics->leftJoin)) {
            foreach ($this->_semantics->leftJoin as $v) {
                $sql .= ' LEFT JOIN ' . $v[0] . ' ON ' . $v[1];
            }
        }
        if (isset($this->_semantics->innerJoin)) {
            foreach ($this->_semantics->innerJoin as $v) {
                $sql .= ' INNER JOIN ' . $v[0] . ' ON ' . $v[1];
            }
        }
        $sql .= isset($this->_semantics->where) ? ' WHERE ' . $this->_semantics->where : '';
        $sql .= isset($this->_semantics->group) ? ' GROUP BY ' . $this->_semantics->group : '';
        $sql .= isset($this->_semantics->having) ? ' HAVING ' . $this->_semantics->having : '';
        $sql .= isset($this->_semantics->order) ? ' ORDER BY ' . $this->_semantics->order : '';
        return $sql;
    }

    /**
     * 绑定参数值
     * @param object SCDbStatement
     * @param array $params
     */
    private function boundValue() {
        if (isset($this->_semantics->value) && is_array($this->_semantics->value)) {
            foreach ($this->_semantics->value as $k => $value) {
                $this->pdo_statement->bindValue(is_int($k) ? $k + 1 : $k, $value);
            }
        }
    }

    /**
     * 获取表结构信息
     * @param string $table 表名
     * @param string $type fields或pk
     */
    public function getTableStruct($table, $type = 'fields') {
        static $structs = array();
        $flag = $this->_connect . '.' . $table;
        if (!isset($structs[$flag])) {
            $shows = $this->execute('SHOW COLUMNS FROM `' . trim($table, '`') . '`')->pdo_statement->fetchAll(SCDb::FETCH_OBJ);
            $fields = array();
            $pk = null;
            foreach ($shows as $k => $v) {
                $fields[] = $v->Field;
                if ('PRI' == $v->Key)
                    $pk = $v->Field;
            }
            $structs[$flag] = compact('pk', 'fields');
        }
        return $structs[$flag][$type];
    }

    private function getField($table) {
        $connect = SCS::config()->connect;
        $prefix = SCS::config()->db[$connect]['prefix'];
        $m = [];
        if (preg_match('/dbname=([^;]+);/i', SCS::config()->db[$connect]['dsn'], $m)) {
            $database = $m[1];
            $table = $database . '.' . $prefix . $table . ';';
            $field = $this->execute('SHOW CREATE TABLE ' . $table)->fetch();

            $mateArray = preg_split("/\n|\r/i", $field['Create Table']);
            $mateArray = array_filter($mateArray);
            array_shift($mateArray);
            array_pop($mateArray);
            array_pop($mateArray);
            $match = [];

            foreach ($mateArray as $k => $v) {
                if (preg_match('/^\s+`([^`]+)/i', $v, $m)) {
                    $match[$k]['field'] = $m[1];
                } else {
                    $match[$k]['field'] = '';
                }
                if (preg_match("/\sdefault\s+'([^']+)'/i", $v, $m)) {
                    $match[$k]['default'] = $m[1];
                } else {
                    $match[$k]['default'] = '';
                }
                if (preg_match("/\scomment\s+'([^']+)'/i", $v, $m)) {
                    $match[$k]['comment'] = $m[1];
                } else {
                    $match[$k]['comment'] = '';
                }
            }
            return $match;
        }else{
            return false;
        }
    }

    /**
     * 对入库数据进行安全转义
     * @param string $str
     * @return string
     */
    protected function escape($str) {
        if (get_magic_quotes_gpc()) {
            $str = stripslashes(trim($str));
        }
        return get_magic_quotes_runtime() ? trim($str) : addslashes(trim($str));
    }

}
